4.3 MySQL

  1. Introduction to PHP Database
    • Read all in PHP MySQL Introduction.
      • What is MySQL?
      • What is the format of databases in MySQL?
      • What is SQL?
      • Is MySQL a part of PHP? Is PHP a part of MySQL? What is the correct relation between them?
      • What kind of queries do you think you need to use for dynamic web applications?

    • How to use MySQL?
      • We will use procedural MySQLi - mysqli_*()s.
      • Three steps:
        1. Connect to a MySQL DB;
        2. manipulate/use the DB with SQL statements;
        3. close the connection
      • Connect? Isn't the web server already connected to the MySQL server?
      • What is a server?
      • Read all in PHP Connect to the MySQL Server.
        • Isn't it dangerous to include username and password in a PHP file?
        • Can you read PHP code from a browser?
          No. Only the execution result will come to the browser. Username and password will not be visible from the browser, as long as they are not echoed.
        • How can you obtain username and password to access a MySQL database?
        • We will use procedural MySQLi.
        • Here is an example to access MySQL database, 'C354_test', on cs.tru.ca. You should not forget that the PHP code accessing MySQL on cs.tru.ca is running on the same system. You also need to know that MySQL running on cs.tru.ca does not accept any access from outside. The host value should be 'localhost', not 'cs.tru.ca'. 
          // mysqli_connect(host, MySQL_username, MySQL_password, db_name)
$conn = mysqli_connect('localhost', 'mySQLusername', 'topmostsecretpassword', 'C354_test');  // not 'cs.tru.ca'; 'C354_test' is a db name.
if (mysqli_connect_errno())  // or if (!$conn)
    echo "Failed to connect to C354_test: " . mysqli_connect_error();
else
    echo "Succeeded to connect to C354_test";
mysqli_close($conn);
        • Trial 1: Let's try to connect to your database on cs.tru.ca. Your MySQL username is the same as your cs.tru.ca username, your MySQL password is your default password, and your MySQL dbname is 'C354_...' ('C354_' + your MySQL username).

        • Can the above username be used to control everything in the MySQL server?

    • What to do with MySQL?
        CRUD (Create, Read, Update, Delete) using a language, SQL (Structured Query Language)
      • Create - Create a DB/table; insert a row into a table
      • Read - Selete rows from a table
      • Update - Update rows in a table
      • Delete - Delete rows/table/DB

    • Read all in PHP Create Database, and PHP Create Tables.
      • SQL statements: CREATE DATABASE; CREATE TABLE
      • Is it safe to allow a user to create a database?
      • When do you use mysqli_query() and mysqli_error()?
      • Read 'MySQL Data Types' in SQL Data Types for Various DBs.
      • Read all in PHP Create MySQL Tables.
      • What are PRIMARY KEY, NOT NULL, and AUTO_INCREMENT?
      • Here is an example to create a table in C354_test. Note that the names of table and columns are NOT string values that use ' ' or " ". 
        // host, user name, password, db name
$conn = mysqli_connect('localhost', ???, ???, 'C354_test');
if (mysqli_connect_errno())
    echo 'Failed to connect: ' . mysqli_connect_error();
else {
    $sql = 'CREATE TABLE Persons(
        SSN INT PRIMARY KEY, 
        FirstName VARCHAR(30) NOT NULL, 
        LastName VARCHAR(30) NOT NULL, 
        Age INT
        )';
    if (mysqli_query($conn, $sql))
        echo 'Table Persons created';
    else
        echo 'Error creating table: ' . mysqli_error($conn);
    mysqli_close($conn);
}
      • Try now to create a table for user management. You can use just two data types - VARCHAR(n) and INT.
      • Trial 2: Let's try to create a table named 'Persons' in your DB.

      • Now we know how to create a table. How can you insert a row into the table?

    • Read all in PHP MySQL Insert Into.
      • What SQL statement is used to insert a row into a table?
      • Here is an example. 
        $conn = mysqli_connect('localhost', ????);
if (mysqli_connect_errno())
    echo 'Failed to connect: ' . mysqli_connect_error();
else {
    $sql = "insert into Persons values ('Dave', 'Smith', 23)";  // Do you remember all the columns?
    if (mysqli_query($conn, $sql))
        echo 'Table Persons updated';
    else
        echo 'Error updating table: ' . mysqli_error($conn);
    $sql = "??? ??? Persons ??? (999888777, 'Tom', ???, 18)";  // Tom Davis
    ???(???, $sql);
    $sql = "??? ??? Persons ??? (888777666, ???, 'Brown', 21)";  // John Brown
    ???($conn, ???);
    $sql = "??? ??? Persons ??? (888777666, 'John', 'Black', 21)";  // What if you insert similar values again?
    ???(???, ???);
    ???($conn);
}
      • Trial 3: Let's try to insert a row into the table for user management..

      • I would like to read tables using a web browser. Is there any convenient tool to admin MySQL?
        • Read the first paragraph in phpMyAdmin.
        • Try now https://cs.tru.ca/phpmyadmin with your MySQL username and password, or with the username and password in the above example.
        • Change your password. (Click 'Server: localhost:...' at the top, and you will see 'Chage password' in 'General settings'.)
        • What else do you think you can do with phpMyAdmin?
      • How can you read a table with PHP? All the rows at once?

    • Read all in PHP MySQL Select.
      • What SQL statement is used to select rows from a table?
      • How to get the rows one by one from the selection result?
      • Here is an example. 
        $conn = ????;
if (mysqli_connect_errno())
    echo 'Failed to connect: ' . mysqli_connect_error();
else {
    $sql = "select FirstName, LastName from Persons";  // or, "select * from Persons"
                                                       // Column names are NOT strings.
    // $sql = "select FirstName, LastName 
    //         from Persons";  // Can you use multiple lines for a string value?
    $result = ????;  // The select query result is NOT an array of selected rows.
    echo mysqli_num_rows($result) . '<br>';  // 
    if (???($result) > 0)  // if the number of rows is > 0
        while ($row = mysqli_fetch_assoc($result))  // mysqli_fetch_assoc() returns an associative array, and mysqli_fetch_array() returns an indexed array.
            echo $row['FirstName'] . " " . ???['LastName'] . '<br>';
            // $row['FirstName'] . " " . $row['LastName'] . " " . $row['Age'] . '<br>';  // What if you try to print Age?
    mysqli_close($conn);
}
      • Why doesn't mysqli_query('select ...') return an array of all the selected rows?
        A lot of rows could be selected.
      • How to count the number of selected rows?
      • Trial 4: Let's try the above 'select' example.

      • How to get all the rows at once?
        • Read all in PHP mysqli_fetch_all() Function.
        • What value is returned from the above function?
        • Is it a really good idea to use the above function? What if the table size is huge?
      • How to store all the fetched rows into an array? 
        $users = array();  // or, $users = [];
while ($row = mysqli_fetch_assoc($result))
    $users[] = $row;  // or array_push(...)?
// or
$users = [];
$i = 0;
while ($row = mysqli_fetch_assoc($result))
    $users[$i++] = $row;  // $users is a linear array of associative arrays.
      • Trial 4.5: Let's update the 'select' example in Trial 4 and save all the selected rows into a linear array.

      • Now you know how to insert a new row and select all rows. What else do you need to know?

    • Read all in SQL WHERE Clause.
      • For what is the where clause used?
      • Here is an example. 
        $conn = mysqli_connect('???', '???', '???', '???');
if (mysqli_connect_errno())
    echo 'Failed to connect to ???: ' . mysqli_connect_error();
else {
    $sql = "select FirstName, LastName ??? Persons 
            where (Age > 20)";  // Which rows will be selected?
    $result = ???($conn, $sql);
    while ($row = ???($result))  // Fetch an associative array
        echo $row['FirstName'] . " " . $row['LastName'];
    mysqli_close($conn);
}
      • Trial 5: Let's try the above example.

      • What operator is used for equality checking?
      • The where clause is like a boolean statement with operators and columns.
      • Here is more information about the where clause - SQL WHERE Clause, SQL AND & OR Operators.
      • Wouldn't it be nice to have a way to obtain sorted rows with a select statement?

    • Read all in SQL ORDER BY Keyword.
      • Here is an example. 
        $conn = mysqli_connect('???', '???', '???', '???');
if (mysqli_connect_errno())
    echo 'Failed to connect to ???: ' . mysqli_connect_error();
else {
    $sql = "select FirstName, LastName 
            from Persons 
            where (Age < 30 and Age > 10)  // Comment here? 
                                           // Not a good idea 
                                           //   because this comment becomes a part of the string.
            order by Age DESC";  // DESC or ASC
    $result = mysqli_query($conn, $sql);
    $persons = [];
    $i = 0;
    while ($row = mysqli_fetch_assoc($result)) {
        $persons[$i] = $row;
        $i++;
    }
    for ($i = 0; $i < count($persons); $i++) {
        foreach($persons[$i] as $k => $v)
            echo $persons[$i][$k] . " => " . $persons[$i][$v] . "<br>";
    }
    mysqli_close($conn);
}
      • How to sort in an ascending order?
      • Can you use multiple columns for sorting?
      • Trial 6: Let's try the above example.
        The difference is to use a function to select rows that are sorted in ascending order. All sorted rows are saved in a linear array.
        Another function is used to construct <table> code from the array.


    • Read all in PHP MySQL Update.
      • How to modify a row or rows in a table?
      • Here is an example to update John Brown's age. 
        $conn = mysqli_connect('???', '???', '???', '???');
if (mysqli_connect_errno())
    echo 'Failed to connect to ???: ' . mysqli_connect_error();
else {
    $sql = "update Persons 
            set Age = 27 
            where (FirstName = 'John' and LastName = ???)";  // Be careful that FirstName is a column name, but its data type is string. So 'John', not John.
    $result = mysqli_query($conn, $sql);  // the data type of $result?
    $result = mysqli_query($conn, "select * from Persons");
    $str = "<table>";
    while ($row = mysqli_fetch_assoc($result)) {
        $str .= ???;
        $str .= "<td>" . $row['FirstName'] . "</td>";
        $str .= ??? . $row[???] . ???;  // LastName
        $str .= ??? . $row['Age'] . ???;
        $str .= "</tr>";
    }
    $str .= ???;
    echo $str;
    mysqli_close($conn);
}
      • Trial 7: Let's try the above example.

      • Now you know how to do CRUD (Create, Retrieve, Update and Delete) operations, i.e., select/insert/update. How can you delete a row?

    • Read all in PHP MySQL Delete
      • Here is an example. 
        $conn = mysqli_connect('???', '???', '???', '???');
if (mysqli_connect_errno())
    echo 'Failed to connect to ???: ' . mysqli_connect_error();
else {
    $sql = "delete from Persons 
            where (FirstName = 'John' and LastName = ???)";  // Delete John Brown's record(s)
    $result = mysqli_query($conn, $sql);
    $result = mysqli_query($conn, "select * from Persons");
    $str = "<table>";
    while ($row = mysqli_fetch_assoc($result)) {
        $str .= ???;
        $str .= "<td>" . $row['FirstName'] . "</td>";
        $str .= ??? . $row[???] . ???;
        $str .= ??? . $row['Age'] . ???;
        $str .= "</tr>";
    }
    $str .= ???;
    echo $str;
    mysqli_close($conn);
}
      • Trial 8: Let's try the above example.

      • How to delete all rows?
        • DELETE FROM table_name;
        • DELETE * FROM table_name;
      • How to delete a table?
        • DROP TABLE table_name; // Only when the table is empty?
      • How to delete a database?
        • DROP DATABASE database_name;

    • To learn more about SQL, visit SQL Tutorial.

  2. Some important questions
    • How to change the table name?
    • How to update columns? For example, adding a new column. Is it a good idea?
    • How to use multiple tables together? For example, two tables - Customers and Orders. How to join the two tables?
    • To learn more about SQL, visit SQL Tutorial.
    • There are many other interesting and useful topics to learn. Let's come back to SQL later for more advanced topics.
    • Can you design a table for 'User Authentication' that will be used in TRUQA?
      • Let's use a table. 'Users'.
        • Columns: Id (primary key, auto increment), Username, Password, Email, Date
        • If you did not create the table, it is about the time to create it using phpMyAdmin.
        • Trial 9: Let's try to check if a user exists in Users. You need to use a function.

        • Trial 10: Let's try to insert a new user and get its user id. You need to use a function.

    • Can you design a table, 'Questions', that will be used in TRUQA? One table? Two tables?
      • Let's use a table. 'Questions'.
        • Columns: Id (primary key), Question, Private, UserId, Date
    • Can you design another table, 'Answers', that will be used in TRUQA?
    • Some very interesting questions
      • Can you write a function to check the validity of a given username and password?
      • How can you retrieve all the questions that include a search term? 
        $sql = "... where Question like '%search-term%'";
      • How to retrieve 10 questions that were posted most recently?
        limit #; limit #,#
      • How can you retrieve all the questions that were posted by a certain user and that include a search term?
      • How can you retrieve all the answers for a particular question?
      • How to retrive all the questions posted by a certain user, with all the answers?

  3. Some review questions and learning outcomes
    • Use of SQL to access MySQL for PHP programming.
    • How to create database and table?
    • How to insert a record in a table?
    • How to delete records from a table?
    • How to select records from a table?
    • Write the PHP code to get all the questions that were posted by a user and send them back to the client in a table form. 
      $sql = "select * from Users where Username = '$username'";
$result = mysqli_query($conn, $sql);
$row = mysqli_fetch_assoc($result);
$userId = $row['Id'];
$sql = "select * from Questions where UserId = $userId";
$result = mysqli_query($conn, $sql);
$table = "<table><tr><th>Question</th><th>Date</th></tr>";
while($row = mysqli_fetch_assoc($result))
    $table .= "<tr><td>" . $row['Question'] . "</td><td>" . $row['Date'] . "</td></tr>";
$table .= "</table>";
echo $table;